import math

import akshare as ak
from lib.conn import Conn
from lib.func import *
import pandas as pd


def base_fields():
    return [
        "ts_code",
        "trade_date",
        "open",
        "high",
        "low",
        "close",
        "pre_close",
        "change",
        "pct_chg",
        "vol",
        "amount"
    ]


def table_suffix(date):
    return '_' + date[:4]


def store_history(s_date):
    conn = Conn()
    rows = conn.fetch_all("select ts_code from td_stock_ak where 1 order by ts_code")
    for row in rows:
        print(row['ts_code'])
        store_weekly_between(row['ts_code'], s_date, today())
        store_daily_between(row['ts_code'], s_date, today())


def store_weekly_between(ts_code, start, end):
    conn = Conn()
    df = ak.stock_zh_a_hist(symbol=ts_code[:6], period="weekly", start_date=start, end_date=end)
    table = 'td_market_weekly'
    i = 0
    for index, row in df.iterrows():
        if row.get('日期') is None:
            continue
        date = row['日期'].strftime('%Y%m%d')
        i += 1
        exist = conn.fetch_one("select id,ts_code from %s where ts_code='%s' and trade_date=%s" % (table, ts_code, date))
        data = {
                'ts_code': ts_code,
                'trade_date': date,
                'open': row['开盘'],
                'high': row['最高'],
                'low': row['最低'],
                'close': row['收盘'],
                'vol': row['成交量'],
                'amount': row['成交额'],
                'change': row['涨跌额'],
                'pct_chg': row['涨跌幅'],
                'turnover_rate': row['换手率'],
            }
        if not exist:
            conn.insert_dict(table, data)
        else:
            conn.update_dict(table, data, {'id': exist['id']})
    print("Total: %d" % i)


def store_daily_between(ts_code, start, end):
    conn = Conn()
    df = ak.stock_zh_a_hist(symbol=ts_code[:6], period="daily", start_date=start, end_date=end)
    i = 0
    for index, row in df.iterrows():
        date = row['日期'].strftime('%Y%m%d')
        i += 1
        table = 'td_market_daily' + table_suffix(date)
        exist = conn.fetch_one("select id,ts_code from %s where ts_code='%s' and trade_date=%s" % (table, ts_code, date))
        data = {
                'ts_code': ts_code,
                'trade_date': date,
                'open': row['开盘'],
                'high': row['最高'],
                'low': row['最低'],
                'close': row['收盘'],
                'vol': row['成交量'],
                'amount': row['成交额'],
                'change': row['涨跌额'],
                'pct_chg': row['涨跌幅'],
                'turnover_rate': row['换手率'],
            }
        if not exist:
            conn.insert_dict(table, data)
        else:
            conn.update_dict(table, data, {'id': exist['id']})
    print("Total: %d" % i)


def sort_name(x):
    return x['trade_date']


def store_fhps():
    df = ak.stock_fhps_em()


def store_trade_date():
    ak.futures_rule()
    df = ak.stock_fhps_em()


def store_stock_basic():
    df = ak.stock_sh_a_spot_em()
    for index, row in df.iterrows():
        ts_code = row['代码'] + '.SH'
        insert_ak_stock(ts_code, row)
    df = ak.stock_sz_a_spot_em()
    for index, row in df.iterrows():
        ts_code = row['代码'] + '.SZ'
        insert_ak_stock(ts_code, row)
    store_industry()


def store_industry():
    conn = Conn()
    stocks = conn.fetch_all("select ts_code from td_stock_ak where industry=''")
    for row in stocks:
        df = ak.stock_individual_info_em(symbol=row['ts_code'][:6])
        conn.update_dict('td_stock_ak', {
            'industry': df.iloc[2, 1]
        }, {'ts_code': row['ts_code']})


def gen_week_data(ts_code):
    conn = Conn()
    start = diff_day(-5)
    end = today()
    sql = 'select * from td_market_daily_{} where ts_code=%s and trade_date between %s and %s order by trade_date'.format(start[:4])
    arr = conn.fetch_all(sql, [ts_code, start, end])
    df = pd.DataFrame(arr)
    exist = conn.fetch_one("select id from td_market_weekly where ts_code='%s' and trade_date=%s" % (ts_code, end))
    data = {
        'ts_code': ts_code,
        'trade_date': end,
        'open': df.iloc[0]['open'],
        'high': df['high'].max(),
        'low': df['low'].min(),
        'close': df.iloc[-1]['close'],
        'vol': df['vol'].sum(),
        'amount': df['amount'].sum(),
        'change': 0,
        'pct_chg': 0,
        'turnover_rate': 0,
    }
    if not exist:
        conn.insert_dict('td_market_weekly', data)
    else:
        conn.update_dict('td_market_weekly', data, {'id': exist['id']})


def insert_ak_stock(ts_code, row):
    if math.isnan(row['最新价']):
        return
    if math.isnan(row['成交量']):
        return
    conn = Conn()
    try:
        if math.isnan(row['量比']):
            row['量比'] = 0
        insert = {
            'ts_code': ts_code,
            'stock_name': row['名称'],
            'price': row['最新价'],
            'pct_chg': row['涨跌幅'],
            'price_chg': row['涨跌额'],
            'vol': row['成交量'],
            'amount': row['成交额'],
            'high': row['最高'],
            'low': row['最低'],
            'open': row['今开'],
            'pre_close': row['昨收'],
            'vol_rate': row['量比'],
            'turnover_rate': row['换手率'],
            'pe': row['市盈率-动态'],
            'pb': row['市净率'],
            'total_value': row['总市值'],
            'float_value': row['流通市值'],
            'pct_chg_60': row['60日涨跌幅'],
            'pct_chg_year': row['年初至今涨跌幅'],
        }
        exist = conn.fetch_one("select id from td_stock_ak where ts_code='%s'" % ts_code)
        if not exist:
            conn.insert_dict('td_stock_ak', insert)
        else:
            conn.update_dict('td_stock_ak', insert, {'id': exist['id']})
        date = today()
        table = 'td_market_daily' + table_suffix(date)
        exist = conn.fetch_one("select id,ts_code from %s where ts_code='%s' and trade_date=%s" % (table, ts_code, date))
        data = {
                'ts_code': ts_code,
                'trade_date': date,
                'open': row['今开'],
                'high': row['最高'],
                'low': row['最低'],
                'close': row['最新价'],
                'vol': row['成交量'],
                'amount': row['成交额'],
                'change': row['涨跌额'],
                'pct_chg': row['涨跌幅'],
                'turnover_rate': row['换手率'],
            }
        if not exist:
            conn.insert_dict(table, data)
        else:
            conn.update_dict(table, data, {'id': exist['id']})
        if weekday() == 5:
            gen_week_data(ts_code)
    except Exception as e:
        raise e


def start_date():
    conn = Conn()
    row = conn.fetch_one("select max(trade_date) as max_date from td_market_daily_index")
    return row['max_date']


def store_index(s_date):
    conn = Conn()
    df = ak.stock_zh_index_daily_em(symbol='sh000300', start_date=s_date, end_date=today())
    for index, row in df.iterrows():
        date = row['date'].replace('-', '')
        table = 'td_market_daily_index'
        ts_code = '000300.SH'
        exist = conn.fetch_one(
            "select id,ts_code from %s where ts_code='%s' and trade_date=%s" % (table, ts_code, date))
        data = {
            'ts_code': ts_code,
            'trade_date': date,
            'open': row['open'],
            'high': row['high'],
            'low': row['low'],
            'close': row['close'],
            'vol': row['volume'],
            'amount': row['amount'],
        }
        if not exist:
            conn.insert_dict(table, data)
        else:
            conn.update_dict(table, data, {'id': exist['id']})
    return None